﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    [Serializable]
    public class D_FIM_MoneyRequestDtls : IDAL_FIM_MoneyRequestDtls
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public D_FIM_MoneyRequestDtls()
        {

        }

        public IList<M_FIM_MoneyRequestDtls> GetFull(Nullable<int> money_req_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequestDtls> infoList = new List<M_FIM_MoneyRequestDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = "SELECT mrd.money_req_dtls_seq, mrd.money_req_seq, mrd.money_req_dtls_name"
                    + ", mrd.bgm_proj_oper_exp_seq, mrd.percent_vat, mrd.percent_tax"
                    + ", mrd.amount, mrd.amount_vat, mrd.amount_tax, mrd.amount_fund, mrd.bath_amount_tax"
                    + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as exclude_vat"

                    + ", recexp.bgm_exp_seq, exp.bgm_exp_name, exp.bgm_exp_grp_seq"
                    + ", exp1.bgm_exp_seq as bgm_exp_seq1, exp1.bgm_exp_name as bgm_exp_name1"
                    + ", exp2.bgm_exp_seq as bgm_exp_seq2, exp2.bgm_exp_name as bgm_exp_name2"

                    + ", mrd.record_status, mrd.remarks"
                    + ", mrd.created_by, mrd.created_when, mrd.updated_by, mrd.updated_when"

                    + ", mrd.ConId, mrd.ref_no, mrd.ref_date, c.NameTh, mrd.branch_no"
                    + " FROM fim_money_request_dtls mrd"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp1 ON exp.upper_bgm_exp_seq = exp1.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp2 ON exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq"
                    + " LEFT JOIN IcmMasContact c on mrd.ConId = c.ConId"
                    + " WHERE mrd.record_status not like @delete_status"
                    + (money_req_seq != null ? " and mrd.money_req_seq = @money_req_seq" : "")
                    + " ORDER BY mrd.money_req_dtls_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequestDtls info = new M_FIM_MoneyRequestDtls();
                        info.money_req_dtls_seq = Convert.ToInt32(drc[i]["money_req_dtls_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.money_req_dtls_name = drc[i]["money_req_dtls_name"].ToString();
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.percent_vat = cn.ConvertNullDouble(drc[i]["percent_vat"]);
                        info.percent_tax = cn.ConvertNullDouble(drc[i]["percent_tax"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.amount_vat = cn.ConvertNullDouble(drc[i]["amount_vat"]);
                        info.amount_tax = cn.ConvertNullDouble(drc[i]["amount_tax"]);
                        info.amount_fund = cn.ConvertNullDouble(drc[i]["amount_fund"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        info.exclude_vat = cn.ConvertNullDouble(drc[i]["exclude_vat"]);
                        info.bath_amount_tax = cn.ConvertNullDouble(drc[i]["bath_amount_tax"]);
                        info.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        info.ConId = cn.ConvertNullInt(drc[i]["ConId"]);
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.ref_date = cn.ConvertNullDateTime(drc[i]["ref_date"]);
                        info.branch_no = drc[i]["branch_no"].ToString();

                        M_BGM_ProjectOperationExpense SubInfo1 = new M_BGM_ProjectOperationExpense();
                        M_BGM_ProjectExpense SubInfo2 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Expense SubInfo3 = new M_BGM_MAS_Expense();
                        M_BGM_MAS_Expense SubInfo5 = new M_BGM_MAS_Expense();
                        M_BGM_MAS_Expense SubInfo6 = new M_BGM_MAS_Expense();

                        SubInfo5.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq1"]);
                        SubInfo5.bgm_exp_name = drc[i]["bgm_exp_name1"].ToString();
                        SubInfo6.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq2"]);
                        SubInfo6.bgm_exp_name = drc[i]["bgm_exp_name2"].ToString();
                        SubInfo5.m_bgm_mas_expense_upper = SubInfo6;
                        SubInfo3.m_bgm_mas_expense_upper = SubInfo5;

                        SubInfo2.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        SubInfo3.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        SubInfo3.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        SubInfo2.m_bgm_mas_expense = SubInfo3;
                        SubInfo1.m_bgm_projectexpense = SubInfo2;
                        info.m_bgm_projectoperationexpense = SubInfo1;

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        //public IList<M_FIM_MoneyRequestDtls> GetFull2(Nullable<int> money_req_seq)
        //{
        //    string strSQL;
        //    DataRowCollection drc = null;
        //    IList<M_FIM_MoneyRequestDtls> infoList = new List<M_FIM_MoneyRequestDtls>();
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

        //        strSQL = "SELECT mrd.money_req_dtls_seq, mrd.money_req_seq, mrd.money_req_dtls_name"
        //            + ", mrd.bgm_proj_oper_exp_seq, mrd.percent_vat, mrd.percent_tax"
        //            + ", mrd.amount, mrd.amount_vat, mrd.amount_tax, mrd.amount_fund, mrd.bath_amount_tax"
        //            + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as exclude_vat"

        //            + ", recexp.bgm_exp_seq, exp.bgm_exp_name, exp.bgm_exp_grp_seq"
        //            + ", exp1.bgm_exp_seq as bgm_exp_seq1, exp1.bgm_exp_name as bgm_exp_name1"
        //            + ", exp2.bgm_exp_seq as bgm_exp_seq2, exp2.bgm_exp_name as bgm_exp_name2"

        //            + ", mrd.record_status, mrd.remarks"
        //            + ", mrd.created_by, mrd.created_when, mrd.updated_by, mrd.updated_when"

        //            + ", mrd.ConId, mrd.ref_no, mrd.ref_date, c.NameTh, mrd.branch_no"
        //            + " FROM fim_money_request_dtls mrd"
        //            + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
        //            + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
        //            + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
        //            + " LEFT JOIN bgm_mas_expense exp1 ON exp.upper_bgm_exp_seq = exp1.bgm_exp_seq"
        //            + " LEFT JOIN bgm_mas_expense exp2 ON exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq"
        //            + " LEFT JOIN IcmMasContact c on mrd.ConId = c.ConId"
        //            + " WHERE mrd.record_status not like @delete_status"
        //            + (money_req_seq != null ? " and mrd.money_req_seq = @money_req_seq" : "")
        //            + " ORDER BY mrd.money_req_dtls_seq";

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_FIM_MoneyRequestDtls info = new M_FIM_MoneyRequestDtls();
        //                info.money_req_dtls_seq = Convert.ToInt32(drc[i]["money_req_dtls_seq"]);
        //                info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
        //                info.money_req_dtls_name = drc[i]["money_req_dtls_name"].ToString();
        //                info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
        //                info.percent_vat = cn.ConvertNullDouble(drc[i]["percent_vat"]);
        //                info.percent_tax = cn.ConvertNullDouble(drc[i]["percent_tax"]);
        //                info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
        //                info.amount_vat = cn.ConvertNullDouble(drc[i]["amount_vat"]);
        //                info.amount_tax = cn.ConvertNullDouble(drc[i]["amount_tax"]);
        //                info.amount_fund = cn.ConvertNullDouble(drc[i]["amount_fund"]);
        //                info.remarks = drc[i]["remarks"].ToString();
        //                info.exclude_vat = cn.ConvertNullDouble(drc[i]["exclude_vat"]);
        //                info.bath_amount_tax = cn.ConvertNullDouble(drc[i]["bath_amount_tax"]);
        //                info.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
        //                info.ConId = cn.ConvertNullInt(drc[i]["ConId"]);
        //                info.contact_name = drc[i]["NameTh"].ToString();
        //                info.ref_no = drc[i]["ref_no"].ToString();
        //                info.ref_date = cn.ConvertNullDateTime(drc[i]["ref_date"]);
        //                info.branch_no = drc[i]["branch_no"].ToString();

        //                M_BGM_ProjectOperationExpense SubInfo1 = new M_BGM_ProjectOperationExpense();
        //                M_BGM_ProjectExpense SubInfo2 = new M_BGM_ProjectExpense();
        //                M_BGM_MAS_Expense SubInfo3 = new M_BGM_MAS_Expense();
        //                M_BGM_MAS_Expense SubInfo5 = new M_BGM_MAS_Expense();
        //                M_BGM_MAS_Expense SubInfo6 = new M_BGM_MAS_Expense();

        //                SubInfo5.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq1"]);
        //                SubInfo5.bgm_exp_name = drc[i]["bgm_exp_name1"].ToString();
        //                SubInfo6.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq2"]);
        //                SubInfo6.bgm_exp_name = drc[i]["bgm_exp_name2"].ToString();
        //                SubInfo5.m_bgm_mas_expense_upper = SubInfo6;
        //                SubInfo3.m_bgm_mas_expense_upper = SubInfo5;

        //                SubInfo2.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
        //                SubInfo3.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
        //                SubInfo3.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
        //                SubInfo2.m_bgm_mas_expense = SubInfo3;
        //                SubInfo1.m_bgm_projectexpense = SubInfo2;
        //                info.m_bgm_projectoperationexpense = SubInfo1;

        //                info.record_status = drc[i]["record_status"].ToString();
        //                info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
        //                info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
        //                info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
        //                info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
        //                infoList.Add(info);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return infoList;
        //}

        public IList<M_FIM_MoneyRequestDtls> GetExpFromProject(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_source_seq, Nullable<int> bgm_exp_grp_seq, Nullable<int> money_req_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequestDtls> infoList = new List<M_FIM_MoneyRequestDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", bgm_exp_grp_seq);
                dbManager.AddParameters(4, "@money_req_seq", money_req_seq);
                
                #region old
                strSQL = "SELECT pjexp.bgm_proj_oper_exp_seq, pjexp.bgm_proj_oper_seq, recexp.bgm_source_seq"
                    + ", coalesce(pjexp.amount, 0) as pro_amount, pjexp.bgm_proj_exp_seq"
                    //+ ", coalesce(used.amount, 0) as used_amount"
                    + ", coalesce(oprpt.cal_amount, 0) as remain_amount"

                    + ", s.bgm_source_name, mrd.remarks"
                    + ", coalesce(rate.exp_rate_count, 0) as exp_rate_count"
                    //+ ", coalesce(rate.class_count, 0) as class_count"
                    + ", recexp.bgm_exp_seq, exp.bgm_exp_name"
                    + ", exp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_name"
                    + ", mrd.money_req_seq, mrd.money_req_dtls_seq"
                    + ", mrd.percent_vat, mrd.percent_tax"
                    + ", mrd.amount, mrd.amount_vat, mrd.amount_tax"
                    + ", mrd.ConId, mrd.ref_no, mrd.ref_date, c.NameTh, mrd.branch_no"

                    + ", exp1.bgm_exp_seq as bgm_exp_seq1, exp1.bgm_exp_name as bgm_exp_name1"
                    + ", exp2.bgm_exp_seq as bgm_exp_seq2, exp2.bgm_exp_name as bgm_exp_name2"

                    + " FROM bgm_project_operation_expense pjexp"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_source s ON recexp.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp1 ON exp.upper_bgm_exp_seq = exp1.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp2 ON exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " LEFT JOIN fim_money_request_dtls mrd ON pjexp.bgm_proj_oper_exp_seq = mrd.bgm_proj_oper_exp_seq"
                        + " AND mrd.money_req_seq = @money_req_seq"
                    //+ " LEFT JOIN fim_mas_currencies cur ON mrd.currency_seq = cur.currency_seq"
                    + " LEFT JOIN (SELECT oprpt.bgm_proj_oper_exp_seq"
                        + ", sum(coalesce(oprpt.cal_amount, 0)) AS cal_amount"
                        + " FROM bgm_project_operation_report oprpt"
                        + " LEFT JOIN fim_money_request_dtls mrd ON oprpt.bgm_proj_oper_exp_seq = mrd.money_req_dtls_seq"
                        + " WHERE oprpt.record_status not like @delete_status"
                        + (money_req_seq != null ? " AND coalesce(mrd.money_req_seq, -1) not like @money_req_seq" : "")
                        + " GROUP BY oprpt.bgm_proj_oper_exp_seq"
                        + ") oprpt ON pjexp.bgm_proj_oper_exp_seq = oprpt.bgm_proj_oper_exp_seq"
                    //+ " LEFT JOIN (SELECT bgm_proj_oper_exp_seq, sum(coalesce(bath_amount, 0)) as amount"
                    //      + " FROM fim_money_request_dtls"
                    //      + " WHERE record_status not like @delete_status"
                    //      + " AND money_req_seq not like @money_req_seq"
                    //      + ") used ON pjexp.bgm_proj_oper_exp_seq = used.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN (SELECT rate.bgm_exp_seq, rate.bgm_source_seq"
                        + ", count(*) as exp_rate_count"//, coalesce(class_count, 0) as class_count"
                        + " FROM fim_mas_expense_rates rate"
                    //+ " LEFT JOIN (SELECT bgm_exp_seq, count(*) as class_count"
                    //    + " FROM fim_mas_expense_rates"
                    //    + " GROUP BY bgm_exp_seq"
                    //    + ") rt ON rate.bgm_exp_seq = rt.bgm_exp_seq"
                        + " WHERE rate.record_status not like @delete_status"
                        + " GROUP BY rate.bgm_exp_seq, rate.bgm_source_seq"
                        + ") rate ON exp.bgm_exp_seq = rate.bgm_exp_seq"
                            + " AND recexp.bgm_source_seq = rate.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c on mrd.ConId = c.ConId"

                    + " WHERE pjexp.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " AND pjexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_source_seq != null ? " AND recexp.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_exp_grp_seq != null ? " AND exp.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "");
                #endregion

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequestDtls info = new M_FIM_MoneyRequestDtls();
                        info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.percent_vat = cn.ConvertNullDouble(drc[i]["percent_vat"]);
                        info.percent_tax = cn.ConvertNullDouble(drc[i]["percent_tax"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.amount_vat = cn.ConvertNullDouble(drc[i]["amount_vat"]);
                        info.amount_tax = cn.ConvertNullDouble(drc[i]["amount_tax"]);
                        info.remarks = drc[i]["remarks"].ToString();

                        info.ConId = cn.ConvertNullInt(drc[i]["ConId"]);
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.ref_date = cn.ConvertNullDateTime(drc[i]["ref_date"]);
                        info.branch_no = drc[i]["branch_no"].ToString();

                        M_BGM_ProjectOperationExpense SubInfo1 = new M_BGM_ProjectOperationExpense();
                        M_BGM_ProjectExpense SubInfo2 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Expense SubInfo3 = new M_BGM_MAS_Expense();
                        M_BGM_MAS_Source SubInfo5 = new M_BGM_MAS_Source();
                        M_BGM_MAS_Expense SubInfo6 = new M_BGM_MAS_Expense();
                        M_BGM_MAS_Expense SubInfo7 = new M_BGM_MAS_Expense();

                        SubInfo1.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        SubInfo1.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        SubInfo1.bgm_proj_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_exp_seq"]);
                        //SubInfo1.used_bath_amount = cn.ConvertNullDouble(drc[i]["used_bath_amount"]);
                        SubInfo1.remain_amount = cn.ConvertNullDouble(drc[i]["remain_amount"]);

                        SubInfo2.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        SubInfo2.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        SubInfo3.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        SubInfo3.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        SubInfo3.bgm_exp_grp_name = drc[i]["bgm_exp_grp_name"].ToString();
                        SubInfo3.exp_rate_count = cn.ConvertNullInt(drc[i]["exp_rate_count"]);
                        //SubInfo3.class_count = cn.ConvertNullInt(drc[i]["class_count"]);

                        SubInfo6.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq1"]);
                        SubInfo6.bgm_exp_name = drc[i]["bgm_exp_name1"].ToString();
                        SubInfo7.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq2"]);
                        SubInfo7.bgm_exp_name = drc[i]["bgm_exp_name2"].ToString();
                        SubInfo6.m_bgm_mas_expense_upper = SubInfo7;
                        SubInfo3.m_bgm_mas_expense_upper = SubInfo6;

                        SubInfo5.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        SubInfo2.m_bgm_mas_expense = SubInfo3;
                        SubInfo2.m_bgm_mas_source = SubInfo5;
                        SubInfo1.m_bgm_projectexpense = SubInfo2;
                        
                        info.m_bgm_projectoperationexpense = SubInfo1;

                        //info.record_status = drc[i]["record_status"].ToString();
                        //info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        //info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        //info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        //info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public double GetExpInApproveProcess(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_source_seq, Nullable<int> bgm_exp_grp_seq, Nullable<int> money_req_seq)
        {
            string strSQL;
            DataTable dtb = null;
            double dAmount = 0;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", bgm_exp_grp_seq);
                dbManager.AddParameters(4, "@money_req_seq", money_req_seq);

                strSQL = "SELECT sum(coalesce(mrd.amount, 0)) as amount"
                    + " FROM fim_money_requests mr"
                    + " LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq"
                    + " left join bgm_project_operation_expense pjexp on mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " WHERE mr.record_status not like @delete_status"
                    + " and mr.record_status not like 'A'"
                    + (bgm_proj_oper_seq != null ? " AND mr.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_source_seq != null ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_exp_grp_seq != null ? " AND exp.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
                    + (money_req_seq != null ? " AND mr.money_req_seq <> @money_req_seq" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    dAmount = cn.ConvertNullDouble(dtb.Rows[0][0]) ?? 0;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dAmount;
        }

        public IList<M_BGM_MAS_Expense> GetExpList(Nullable<int> money_req_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_MAS_Expense> infoList = new List<M_BGM_MAS_Expense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = "SELECT exp.bgm_exp_seq, exp.bgm_exp_name"
                    + " FROM fim_money_request_dtls mrd"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " WHERE mrd.record_status not like @delete_status"
                    + (money_req_seq != null ? " and mrd.money_req_seq = @money_req_seq" : "")
                    + " ORDER BY exp.bgm_exp_name";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_MAS_Expense info = new M_BGM_MAS_Expense();
                        info.bgm_exp_seq = Convert.ToInt32(drc[i]["bgm_exp_seq"]);
                        info.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MoneyRequestDtls> GetList(Nullable<int> money_req_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MoneyRequestDtls> infoList = new List<M_FIM_MoneyRequestDtls>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = "SELECT mrd.money_req_dtls_seq, exp.bgm_exp_name as money_req_dtls_name"
                    + " FROM fim_money_request_dtls mrd"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " WHERE mrd.record_status not like @delete_status"
                    + (money_req_seq != null ? " and mrd.money_req_seq = @money_req_seq" : "")
                    + " ORDER BY mrd.money_req_dtls_seq";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MoneyRequestDtls info = new M_FIM_MoneyRequestDtls();
                        info.money_req_dtls_seq = Convert.ToInt32(drc[i]["money_req_dtls_seq"]);
                        info.money_req_dtls_name = drc[i]["money_req_dtls_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MoneyRequestDtls GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MoneyRequestDtls info = new M_FIM_MoneyRequestDtls();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT mrd.money_req_dtls_seq, mrd.money_req_seq, mrd.money_req_dtls_name"
                    + ", mrd.bgm_proj_oper_exp_seq, mrd.percent_vat, mrd.percent_tax"
                    + ", mrd.amount, mrd.amount_vat, mrd.amount_tax, mrd.amount_fund, mrd.bath_amount_tax"
                    + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as exclude_vat"

                    + ", recexp.bgm_proj_exp_seq, recexp.bgm_exp_seq, exp.bgm_exp_name, exp.bgm_exp_grp_seq"
                    + ", mrd.record_status, mrd.remarks"
                    + ", mrd.created_by, mrd.created_when, mrd.updated_by, mrd.updated_when"

                    + ", mrd.ConId, mrd.ref_no, mrd.ref_date, c.NameTh, mrd.branch_no"
                    + " FROM fim_money_request_dtls mrd"
                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN IcmMasContact c on mrd.ConId = c.ConId"
                    + " WHERE mrd.money_req_dtls_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.money_req_dtls_seq = Convert.ToInt32(drc[0]["money_req_dtls_seq"]);
                    info.money_req_seq = cn.ConvertNullInt(drc[0]["money_req_seq"]);
                    info.money_req_dtls_name = drc[0]["money_req_dtls_name"].ToString();
                    info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_exp_seq"]);
                    info.percent_vat = cn.ConvertNullDouble(drc[0]["percent_vat"]);
                    info.percent_tax = cn.ConvertNullDouble(drc[0]["percent_tax"]);
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.amount_vat = cn.ConvertNullDouble(drc[0]["amount_vat"]);
                    info.amount_tax = cn.ConvertNullDouble(drc[0]["amount_tax"]);
                    info.amount_fund = cn.ConvertNullDouble(drc[0]["amount_fund"]);
                    info.remarks = drc[0]["remarks"].ToString();
                    info.bath_amount_tax = cn.ConvertNullDouble(drc[0]["bath_amount_tax"]);
                    info.exclude_vat = cn.ConvertNullDouble(drc[0]["exclude_vat"]);
                    info.ConId = cn.ConvertNullInt(drc[0]["ConId"]);
                    info.contact_name = drc[0]["NameTh"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.ref_date = cn.ConvertNullDateTime(drc[0]["ref_date"]);
                    info.branch_no = drc[0]["branch_no"].ToString();

                    M_BGM_ProjectOperationExpense SubInfo1 = new M_BGM_ProjectOperationExpense();
                    M_BGM_ProjectExpense SubInfo2 = new M_BGM_ProjectExpense();
                    M_BGM_MAS_Expense SubInfo3 = new M_BGM_MAS_Expense();

                    SubInfo1.bgm_proj_exp_seq = cn.ConvertNullInt(drc[0]["bgm_proj_exp_seq"]);
                    SubInfo3.bgm_exp_name = drc[0]["bgm_exp_name"].ToString();
                    SubInfo3.bgm_exp_grp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_grp_seq"]);
                    SubInfo2.m_bgm_mas_expense = SubInfo3;
                    SubInfo1.m_bgm_projectexpense = SubInfo2;
                    info.m_bgm_projectoperationexpense = SubInfo1;

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq from fim_money_request_dtls";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_MoneyRequestDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(25);

                dbManager.AddParameters(0, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(1, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(2, "@money_req_dtls_name", info.money_req_dtls_name);
                dbManager.AddParameters(3, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(4, "@percent_vat", info.percent_vat);
                dbManager.AddParameters(5, "@percent_tax", info.percent_tax);
                dbManager.AddParameters(6, "@amount", info.amount);
                dbManager.AddParameters(7, "@amount_vat", info.amount_vat);
                dbManager.AddParameters(8, "@amount_tax", info.amount_tax);
                dbManager.AddParameters(9, "@amount_fund", info.amount_fund);
                dbManager.AddParameters(10, "@currency_seq", "");
                dbManager.AddParameters(11, "@exchange_rate", "");
                dbManager.AddParameters(12, "@bath_amount", "");
                dbManager.AddParameters(13, "@bath_amount_vat", "");
                dbManager.AddParameters(14, "@bath_amount_tax", info.bath_amount_tax);
                dbManager.AddParameters(15, "@bath_amount_fund", "");

                dbManager.AddParameters(16, "@job_site_seq", info.job_site_seq);

                dbManager.AddParameters(17, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(18, "@created_by", info.created_by);
                dbManager.AddParameters(19, "@created_when", info.created_when);

                dbManager.AddParameters(20, "@remarks", info.remarks);
                dbManager.AddParameters(21, "@ConId", info.ConId);
                dbManager.AddParameters(22, "@ref_no", info.ref_no);
                dbManager.AddParameters(23, "@ref_date", info.ref_date);
                dbManager.AddParameters(24, "@branch_no", info.branch_no);

                strSQL = "INSERT INTO fim_money_request_dtls"
                    + "(money_req_seq, money_req_dtls_name"
                    + ", bgm_proj_oper_exp_seq, percent_vat, percent_tax"
                    + ", amount, amount_vat, amount_tax, amount_fund, bath_amount_tax"
                    //+ ", currency_seq, exchange_rate"
                    //+ ", bath_amount, bath_amount_vat, bath_amount_tax, bath_amount_fund"
                    + ", job_site_seq, remarks"
                    + ", record_status, created_by, created_when, ConId, ref_no, ref_date, branch_no)"
                    + " VALUES (@money_req_seq"
                        + ", @money_req_dtls_name"
                        + ", @bgm_proj_oper_exp_seq"
                        + ", @percent_vat"
                        + ", @percent_tax"
                        + ", @amount"
                        + ", @amount_vat"
                        + ", @amount_tax"
                        + ", @amount_fund"
                        //+ ", @currency_seq"
                        //+ ", @exchange_rate"                        
                        //+ ", @bath_amount"
                        //+ ", @bath_amount_vat"
                        + ", @bath_amount_tax"
                        //+ ", @bath_amount_fund"
                        + ", @job_site_seq"
                        + ", @remarks"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ", @ConId"
                        + ", @ref_no"
                        + ", @ref_date"
                        + ", @branch_no"
                        + ");"
                    + " select scope_identity()";
                var strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                return strResult;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MoneyRequestDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(25);

                dbManager.AddParameters(0, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(1, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(2, "@money_req_dtls_name", info.money_req_dtls_name);
                dbManager.AddParameters(3, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(4, "@percent_vat", info.percent_vat);
                dbManager.AddParameters(5, "@percent_tax", info.percent_tax);
                dbManager.AddParameters(6, "@amount", info.amount);
                dbManager.AddParameters(7, "@amount_vat", info.amount_vat);
                dbManager.AddParameters(8, "@amount_tax", info.amount_tax);
                dbManager.AddParameters(9, "@amount_fund", info.amount_fund);
                dbManager.AddParameters(10, "@currency_seq", "");
                dbManager.AddParameters(11, "@exchange_rate", "");
                dbManager.AddParameters(12, "@bath_amount", "");
                dbManager.AddParameters(13, "@bath_amount_vat", "");
                dbManager.AddParameters(14, "@bath_amount_tax", info.bath_amount_tax);
                dbManager.AddParameters(15, "@bath_amount_fund", "");

                dbManager.AddParameters(16, "@job_site_seq", info.job_site_seq);

                dbManager.AddParameters(17, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(18, "@updated_by", info.updated_by);
                dbManager.AddParameters(19, "@updated_when", info.updated_when);

                dbManager.AddParameters(20, "@remarks", info.remarks);
                dbManager.AddParameters(21, "@ConId", info.ConId);
                dbManager.AddParameters(22, "@ref_no", info.ref_no);
                dbManager.AddParameters(23, "@ref_date", info.ref_date);
                dbManager.AddParameters(24, "@branch_no", info.branch_no);

                strSQL = "UPDATE fim_money_request_dtls"
                    + " SET money_req_seq = @money_req_seq"
                    + ", money_req_dtls_name = @money_req_dtls_name"
                    + ", bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq"
                    + ", percent_vat = @percent_vat"
                    + ", percent_tax = @percent_tax"
                    + ", amount = @amount"
                    + ", amount_vat = @amount_vat"
                    + ", amount_tax = @amount_tax"
                    + ", amount_fund = @amount_fund"
                    //+ ", currency_seq = @currency_seq"
                    //+ ", exchange_rate = @exchange_rate"
                    //+ ", bath_amount = @bath_amount"
                    //+ ", bath_amount_vat = @bath_amount_vat"
                    + ", bath_amount_tax = @bath_amount_tax"
                    //+ ", bath_amount_fund = @bath_amount_fund"
                    + ", job_site_seq = @job_site_seq"
                    + ", remarks = @remarks"
                    
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + ", ConId = @ConId"
                    + ", ref_no = @ref_no"
                    + ", ref_date = @ref_date"
                    + ", branch_no = @branch_no"
                    + " WHERE money_req_dtls_seq = @money_req_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MoneyRequestDtls info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_money_request_dtls"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE money_req_dtls_seq = @money_req_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
